A database is a collection of data. A user typically stores, retrieves and modifies the data in a database using a computer program, such as a database management system (DBMS).
One type of DBMS is a relational data base management system, which stores information in tables. A table is a series of intersecting rows and columns. The rows of a table typically represent records, which are collections of information about particular items, and the columns typically represent fields, which specify particular attributes of a record—e.g., a particular type of data that is contained in each field of the record. Each field contains the data having the particular attribute for the intersecting row and column.
The data stored in the tables of a relational database is commonly accessed and retrieved using a query and analysis tool. For example, a user can use the tool to perform specific operations on the tables, rows, and on individual data elements. One type of operation is an aggregation operation, and one such aggregation function is called a distinct count.
Distinct counts are a very important and common analytics requirement. For a detail table comprised of rows that are divided into columns, a distinct count indicates the number of unique values appearing in a first column of rows that satisfy zero or more conditions on other columns. For example, for an Ordered Items detailed table that contains a row for each item that has been ordered by a customer and, which is divided into a customer ID column, an item ID column, an order date column, and a customer zip code column, a user may wish to count the number of different user IDs contained in the rows containing the zip code 98210.
Conventional, known methods of performing a distinct count metric are unable to use aggregate navigation to perform the distinct count. To the contrary, these conventional methods always obtain the distinct count metric from the most detailed source, resulting in significant performance bottlenecks. A major performance problem is generally encountered while using conventional methods to perform distinct count metrics in an analytics environment because, by their very nature, these conventional methods of distinct counting are more than an order or magnitude slower than other aggregation functions. This problem is compounded when there is more than one distinct count measured in a report.
Accordingly, a technique for performing a distinct count metric that utilizes aggregate navigation will have significant utility.